{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/gazette/UK%20Gazette.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Install dependencies\n",
        "!pip install neo4j beautifulsoup4 spacy pandas\n",
        "!python -m spacy download en_core_web_sm"
      ],
      "metadata": {
        "id": "txlShJzKjG4k",
        "outputId": "d88955da-8fc3-4a4d-c586-2f86caca6134",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "id": "txlShJzKjG4k",
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting neo4j\n",
            "  Downloading neo4j-5.5.0.tar.gz (171 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m171.0/171.0 KB\u001b[0m \u001b[31m3.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Installing build dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Getting requirements to build wheel ... \u001b[?25l\u001b[?25hdone\n",
            "  Installing backend dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Preparing metadata (pyproject.toml) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[33m  WARNING: Generating metadata for package neo4j produced metadata for project name unknown. Fix your #egg=neo4j fragments.\u001b[0m\u001b[33m\n",
            "\u001b[0mDiscarding \u001b[4;34mhttps://files.pythonhosted.org/packages/d3/b7/f1f8ffdb12d7e10d31b0a23bc2d8a78ad4659474eda2a62f023717df2ba1/neo4j-5.5.0.tar.gz#sha256=2632386380b2ebb7d6a80e4186899ef342ef0507601c65e200696f13742046b8 (from https://pypi.org/simple/neo4j/) (requires-python:>=3.7)\u001b[0m: \u001b[33mRequested unknown from https://files.pythonhosted.org/packages/d3/b7/f1f8ffdb12d7e10d31b0a23bc2d8a78ad4659474eda2a62f023717df2ba1/neo4j-5.5.0.tar.gz#sha256=2632386380b2ebb7d6a80e4186899ef342ef0507601c65e200696f13742046b8 has inconsistent name: filename has 'neo4j', but metadata has 'unknown'\u001b[0m\n",
            "  Downloading neo4j-5.4.0.tar.gz (162 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m162.5/162.5 KB\u001b[0m \u001b[31m18.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Installing build dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Getting requirements to build wheel ... \u001b[?25l\u001b[?25hdone\n",
            "  Installing backend dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Preparing metadata (pyproject.toml) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[33m  WARNING: Generating metadata for package neo4j produced metadata for project name unknown. Fix your #egg=neo4j fragments.\u001b[0m\u001b[33m\n",
            "\u001b[0mDiscarding \u001b[4;34mhttps://files.pythonhosted.org/packages/01/b1/f0fe9bc5f59d54bd0ff74afdfdc58911aaab53315f2ed40087b018eb54f8/neo4j-5.4.0.tar.gz#sha256=50293f716412cc8a0fc87c364b0da105aa439859d28ffa5c89f5ca0d44514049 (from https://pypi.org/simple/neo4j/) (requires-python:>=3.7)\u001b[0m: \u001b[33mRequested unknown from https://files.pythonhosted.org/packages/01/b1/f0fe9bc5f59d54bd0ff74afdfdc58911aaab53315f2ed40087b018eb54f8/neo4j-5.4.0.tar.gz#sha256=50293f716412cc8a0fc87c364b0da105aa439859d28ffa5c89f5ca0d44514049 has inconsistent name: filename has 'neo4j', but metadata has 'unknown'\u001b[0m\n",
            "  Downloading neo4j-5.3.0.tar.gz (157 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m157.8/157.8 KB\u001b[0m \u001b[31m17.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Installing build dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Getting requirements to build wheel ... \u001b[?25l\u001b[?25hdone\n",
            "  Installing backend dependencies ... \u001b[?25l\u001b[?25hdone\n",
            "  Preparing metadata (pyproject.toml) ... \u001b[?25l\u001b[?25hdone\n",
            "\u001b[33m  WARNING: Generating metadata for package neo4j produced metadata for project name unknown. Fix your #egg=neo4j fragments.\u001b[0m\u001b[33m\n",
            "\u001b[0mDiscarding \u001b[4;34mhttps://files.pythonhosted.org/packages/56/84/c4ac67441e6f2c68e859e64631d6a218a922a2aca0603dcf96ff99f17734/neo4j-5.3.0.tar.gz#sha256=0c1c7d8812eed60da0a442d1e0f35edbda248255703e506a081cb70e083b3b5c (from https://pypi.org/simple/neo4j/) (requires-python:>=3.7)\u001b[0m: \u001b[33mRequested unknown from https://files.pythonhosted.org/packages/56/84/c4ac67441e6f2c68e859e64631d6a218a922a2aca0603dcf96ff99f17734/neo4j-5.3.0.tar.gz#sha256=0c1c7d8812eed60da0a442d1e0f35edbda248255703e506a081cb70e083b3b5c has inconsistent name: filename has 'neo4j', but metadata has 'unknown'\u001b[0m\n",
            "  Downloading neo4j-5.2.1.tar.gz (174 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m174.2/174.2 KB\u001b[0m \u001b[31m19.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "Requirement already satisfied: beautifulsoup4 in /usr/local/lib/python3.8/dist-packages (4.6.3)\n",
            "Requirement already satisfied: spacy in /usr/local/lib/python3.8/dist-packages (3.4.4)\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.8/dist-packages (1.3.5)\n",
            "Requirement already satisfied: pytz in /usr/local/lib/python3.8/dist-packages (from neo4j) (2022.7)\n",
            "Requirement already satisfied: tqdm<5.0.0,>=4.38.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (4.64.1)\n",
            "Requirement already satisfied: thinc<8.2.0,>=8.1.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (8.1.6)\n",
            "Requirement already satisfied: jinja2 in /usr/local/lib/python3.8/dist-packages (from spacy) (2.11.3)\n",
            "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (21.3)\n",
            "Requirement already satisfied: wasabi<1.1.0,>=0.9.1 in /usr/local/lib/python3.8/dist-packages (from spacy) (0.10.1)\n",
            "Requirement already satisfied: spacy-loggers<2.0.0,>=1.0.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (1.0.4)\n",
            "Requirement already satisfied: pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4 in /usr/local/lib/python3.8/dist-packages (from spacy) (1.10.4)\n",
            "Requirement already satisfied: cymem<2.1.0,>=2.0.2 in /usr/local/lib/python3.8/dist-packages (from spacy) (2.0.7)\n",
            "Requirement already satisfied: pathy>=0.3.5 in /usr/local/lib/python3.8/dist-packages (from spacy) (0.10.1)\n",
            "Requirement already satisfied: typer<0.8.0,>=0.3.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (0.7.0)\n",
            "Requirement already satisfied: srsly<3.0.0,>=2.4.3 in /usr/local/lib/python3.8/dist-packages (from spacy) (2.4.5)\n",
            "Requirement already satisfied: langcodes<4.0.0,>=3.2.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (3.3.0)\n",
            "Requirement already satisfied: preshed<3.1.0,>=3.0.2 in /usr/local/lib/python3.8/dist-packages (from spacy) (3.0.8)\n",
            "Requirement already satisfied: smart-open<7.0.0,>=5.2.1 in /usr/local/lib/python3.8/dist-packages (from spacy) (6.3.0)\n",
            "Requirement already satisfied: murmurhash<1.1.0,>=0.28.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (1.0.9)\n",
            "Requirement already satisfied: spacy-legacy<3.1.0,>=3.0.10 in /usr/local/lib/python3.8/dist-packages (from spacy) (3.0.11)\n",
            "Requirement already satisfied: setuptools in /usr/local/lib/python3.8/dist-packages (from spacy) (57.4.0)\n",
            "Requirement already satisfied: numpy>=1.15.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (1.21.6)\n",
            "Requirement already satisfied: catalogue<2.1.0,>=2.0.6 in /usr/local/lib/python3.8/dist-packages (from spacy) (2.0.8)\n",
            "Requirement already satisfied: requests<3.0.0,>=2.13.0 in /usr/local/lib/python3.8/dist-packages (from spacy) (2.25.1)\n",
            "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.8/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.8/dist-packages (from packaging>=20.0->spacy) (3.0.9)\n",
            "Requirement already satisfied: typing-extensions>=4.2.0 in /usr/local/lib/python3.8/dist-packages (from pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4->spacy) (4.4.0)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.8/dist-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)\n",
            "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy) (1.24.3)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy) (2022.12.7)\n",
            "Requirement already satisfied: chardet<5,>=3.0.2 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy) (4.0.0)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy) (2.10)\n",
            "Requirement already satisfied: confection<1.0.0,>=0.0.1 in /usr/local/lib/python3.8/dist-packages (from thinc<8.2.0,>=8.1.0->spacy) (0.0.3)\n",
            "Requirement already satisfied: blis<0.8.0,>=0.7.8 in /usr/local/lib/python3.8/dist-packages (from thinc<8.2.0,>=8.1.0->spacy) (0.7.9)\n",
            "Requirement already satisfied: click<9.0.0,>=7.1.1 in /usr/local/lib/python3.8/dist-packages (from typer<0.8.0,>=0.3.0->spacy) (7.1.2)\n",
            "Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.8/dist-packages (from jinja2->spacy) (2.0.1)\n",
            "Building wheels for collected packages: neo4j\n",
            "  Building wheel for neo4j (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for neo4j: filename=neo4j-5.2.1-py3-none-any.whl size=249499 sha256=aa4077b7e3b58ffc1f318b09f3de891a1a906dd6cda2744d91a6da67e4640dcb\n",
            "  Stored in directory: /root/.cache/pip/wheels/3f/cb/09/5c56a88c1650f9d4fe57e59d9a67a5d77fcef88f55d5c233f1\n",
            "Successfully built neo4j\n",
            "Installing collected packages: neo4j\n",
            "Successfully installed neo4j-5.2.1\n",
            "/usr/local/lib/python3.8/dist-packages/torch/cuda/__init__.py:497: UserWarning: Can't initialize NVML\n",
            "  warnings.warn(\"Can't initialize NVML\")\n",
            "2023-02-01 12:06:40.776865: E tensorflow/stream_executor/cuda/cuda_driver.cc:271] failed call to cuInit: CUDA_ERROR_NO_DEVICE: no CUDA-capable device is detected\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting en-core-web-sm==3.4.1\n",
            "  Downloading https://github.com/explosion/spacy-models/releases/download/en_core_web_sm-3.4.1/en_core_web_sm-3.4.1-py3-none-any.whl (12.8 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m12.8/12.8 MB\u001b[0m \u001b[31m40.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: spacy<3.5.0,>=3.4.0 in /usr/local/lib/python3.8/dist-packages (from en-core-web-sm==3.4.1) (3.4.4)\n",
            "Requirement already satisfied: pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (1.10.4)\n",
            "Requirement already satisfied: spacy-legacy<3.1.0,>=3.0.10 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (3.0.11)\n",
            "Requirement already satisfied: preshed<3.1.0,>=3.0.2 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (3.0.8)\n",
            "Requirement already satisfied: catalogue<2.1.0,>=2.0.6 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.0.8)\n",
            "Requirement already satisfied: cymem<2.1.0,>=2.0.2 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.0.7)\n",
            "Requirement already satisfied: wasabi<1.1.0,>=0.9.1 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (0.10.1)\n",
            "Requirement already satisfied: jinja2 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.11.3)\n",
            "Requirement already satisfied: requests<3.0.0,>=2.13.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.25.1)\n",
            "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (21.3)\n",
            "Requirement already satisfied: srsly<3.0.0,>=2.4.3 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.4.5)\n",
            "Requirement already satisfied: langcodes<4.0.0,>=3.2.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (3.3.0)\n",
            "Requirement already satisfied: spacy-loggers<2.0.0,>=1.0.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (1.0.4)\n",
            "Requirement already satisfied: thinc<8.2.0,>=8.1.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (8.1.6)\n",
            "Requirement already satisfied: setuptools in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (57.4.0)\n",
            "Requirement already satisfied: pathy>=0.3.5 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (0.10.1)\n",
            "Requirement already satisfied: typer<0.8.0,>=0.3.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (0.7.0)\n",
            "Requirement already satisfied: tqdm<5.0.0,>=4.38.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (4.64.1)\n",
            "Requirement already satisfied: numpy>=1.15.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (1.21.6)\n",
            "Requirement already satisfied: murmurhash<1.1.0,>=0.28.0 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (1.0.9)\n",
            "Requirement already satisfied: smart-open<7.0.0,>=5.2.1 in /usr/local/lib/python3.8/dist-packages (from spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (6.3.0)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.8/dist-packages (from packaging>=20.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (3.0.9)\n",
            "Requirement already satisfied: typing-extensions>=4.2.0 in /usr/local/lib/python3.8/dist-packages (from pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (4.4.0)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2022.12.7)\n",
            "Requirement already satisfied: chardet<5,>=3.0.2 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (4.0.0)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.10)\n",
            "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.8/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (1.24.3)\n",
            "Requirement already satisfied: blis<0.8.0,>=0.7.8 in /usr/local/lib/python3.8/dist-packages (from thinc<8.2.0,>=8.1.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (0.7.9)\n",
            "Requirement already satisfied: confection<1.0.0,>=0.0.1 in /usr/local/lib/python3.8/dist-packages (from thinc<8.2.0,>=8.1.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (0.0.3)\n",
            "Requirement already satisfied: click<9.0.0,>=7.1.1 in /usr/local/lib/python3.8/dist-packages (from typer<0.8.0,>=0.3.0->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (7.1.2)\n",
            "Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.8/dist-packages (from jinja2->spacy<3.5.0,>=3.4.0->en-core-web-sm==3.4.1) (2.0.1)\n",
            "\u001b[38;5;2m✔ Download and installation successful\u001b[0m\n",
            "You can now load the package via spacy.load('en_core_web_sm')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Update\n",
        "\n",
        "* Updates to GDS 2.3.0 and Neo4j v5"
      ],
      "metadata": {
        "id": "ZE1qb6034DSM"
      },
      "id": "ZE1qb6034DSM"
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Represent United Kingdom's public record as a knowledge graph\n",
        "## Utilize UK Gazette API capabilities to construct a knowledge graph and analyze it in Neo4j\n",
        "I love constructing knowledge graphs from various sources. I've wanted to create a government knowledge graph for some time now but was struggling to find any data that is easily accessible and doesn't require me to spend weeks developing a data pipeline. At first, I thought I would have to use OCR and NLP techniques to extract valuable information from public records, but luckily I stumbled upon UK Gazette. The UK Gazette is a website that holds the United Kingdom's official public record information. All the content on the website and via its APIs is available under the Open Government License v3.0. Since they offer the public record information through an API endpoint, we don't have to use any scraping tool to extract the information. Even more impressive is that you can export the data in linked data format (RDF). Linked data is a format to represent structured data which is interlinked and essentially represents a graph as you are dealing with nodes and relationships.\n",
        "\n",
        "Since the linked data structure (RDF) already contains information about nodes and relationships, we don't have to define a graph schema manually.\n",
        "Most graph databases use either the RDF (Resource Description Framework) or the LPG (Labeled-property graph) model under the hood. If you are using an RDF graph database, the structure of the linked data information will be identical to the graph model in the database. However, as you might know from my previous posts, I like to use Neo4j, which utilizes an LPG graph model. I won't go much into the difference between the two models here. If you want to learn more about the difference between the RDF and LPG models, I would point you to the [presentation by Jesús Barrasa](https://neo4j.com/blog/rdf-triple-store-vs-labeled-property-graph-difference/).\n",
        "Since the linked data structure is frequently used to transmit data, the folks at Neo4j have made it easy to import or export data in the linked data format by using the [Neosemantics library](https://neo4j.com/labs/neosemantics/). In this post, we will be using the Neo4j database in combination with the Neosemantics library to store the linked data information fetched from the UK Gazette's API.\n",
        "## Environment setup\n",
        "To follow along, you need to have a running instance of the Neo4j database with the Neosemantics library installed.\n",
        "One option is to use the Neo4j Sandbox environment, a free cloud instance of the Neo4j database with the Neosemantics library pre-installed. If you want to use the Neo4j Sandbox environment, [start a blank project](https://sandbox.neo4j.com/?usecase=blank-sandbox) that comes with an empty database.\n",
        "On the other hand, you could also use a local environment of Neo4j. If you opt for a local version, I recommend using the Neo4j Desktop application, a database management application that has a simple interface for adding plugins with a single click.\n",
        "## Setting up connection to Neo4j instance\n",
        "Before we begin, we have to establish connection with Neo4j from the notebook environment. If you are using the Sandbox instance, you can copy details from the Connection Details tab."
      ],
      "metadata": {
        "id": "jtY2eCcvjqRP"
      },
      "id": "jtY2eCcvjqRP"
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "id": "a1280b35",
      "metadata": {
        "id": "a1280b35"
      },
      "outputs": [],
      "source": [
        "# Define Neo4j connections\n",
        "import pandas as pd\n",
        "from neo4j import GraphDatabase\n",
        "host = 'bolt://3.231.25.240:7687'\n",
        "user = 'neo4j'\n",
        "password = 'hatchets-visitor-axes'\n",
        "driver = GraphDatabase.driver(host,auth=(user, password))\n",
        "\n",
        "def run_query(query, params={}):\n",
        "    with driver.session() as session:\n",
        "        result = session.run(query, params)\n",
        "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Configuring Neosemantics library\n",
        "It is required to define a unique constraint on the Resource nodes for the Neosemantics library to work. You can define the unique constraint using the following Cypher statement."
      ],
      "metadata": {
        "id": "rMNbjHt2kmq5"
      },
      "id": "rMNbjHt2kmq5"
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "81cad3ea",
      "metadata": {
        "id": "81cad3ea",
        "outputId": "cff44a5c-79a8-4c0a-8a72-b64816ecc7c7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 49
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: []\n",
              "Index: []"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-7536bcec-00cd-49b8-8206-c8cbea6f9925\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7536bcec-00cd-49b8-8206-c8cbea6f9925')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-7536bcec-00cd-49b8-8206-c8cbea6f9925 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-7536bcec-00cd-49b8-8206-c8cbea6f9925');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "CREATE CONSTRAINT n10s_unique_uri IF NOT EXISTS FOR (r:Resource)\n",
        "REQUIRE r.uri IS UNIQUE\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Next, we need to define the Neosemantics configuration. We have a couple of options to specify how we want the RDF data to be imported as an LPG graph. We'll keep most of the configuration default and only set the handleVocabUri and applyNeo4jNaming parameters. Again, you can inspect the documentation for the [complete reference of configuration options](https://neo4j.com/labs/neosemantics/4.3/reference/).\n",
        "\n",
        "Use the following Cypher statement to define the Neosemantics configuration."
      ],
      "metadata": {
        "id": "-DcpzLqHkpr8"
      },
      "id": "-DcpzLqHkpr8"
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "6523f9ac",
      "metadata": {
        "id": "6523f9ac",
        "outputId": "ba1508ad-9a20-41a1-9efc-5a21588c8f9e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 520
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                    param                  value\n",
              "0         handleVocabUris                    MAP\n",
              "1          handleMultival              OVERWRITE\n",
              "2          handleRDFTypes                 LABELS\n",
              "3             keepLangTag                  False\n",
              "4     keepCustomDataTypes                  False\n",
              "5        applyNeo4jNaming                   True\n",
              "6     baseSchemaNamespace  neo4j://graph.schema#\n",
              "7        baseSchemaPrefix                  n4sch\n",
              "8              classLabel                  Class\n",
              "9           subClassOfRel                    SCO\n",
              "10  dataTypePropertyLabel               Property\n",
              "11    objectPropertyLabel           Relationship\n",
              "12       subPropertyOfRel                    SPO\n",
              "13              domainRel                 DOMAIN\n",
              "14               rangeRel                  RANGE"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-52069198-d771-411d-a789-9b475955dc02\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>param</th>\n",
              "      <th>value</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>handleVocabUris</td>\n",
              "      <td>MAP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>handleMultival</td>\n",
              "      <td>OVERWRITE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>handleRDFTypes</td>\n",
              "      <td>LABELS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>keepLangTag</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>keepCustomDataTypes</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>applyNeo4jNaming</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>baseSchemaNamespace</td>\n",
              "      <td>neo4j://graph.schema#</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>baseSchemaPrefix</td>\n",
              "      <td>n4sch</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>classLabel</td>\n",
              "      <td>Class</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>subClassOfRel</td>\n",
              "      <td>SCO</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>dataTypePropertyLabel</td>\n",
              "      <td>Property</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>objectPropertyLabel</td>\n",
              "      <td>Relationship</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>subPropertyOfRel</td>\n",
              "      <td>SPO</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>domainRel</td>\n",
              "      <td>DOMAIN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>rangeRel</td>\n",
              "      <td>RANGE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-52069198-d771-411d-a789-9b475955dc02')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-52069198-d771-411d-a789-9b475955dc02 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-52069198-d771-411d-a789-9b475955dc02');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "CALL n10s.graphconfig.init({\n",
        "  handleVocabUris: 'MAP',\n",
        "  applyNeo4jNaming: true\n",
        "})\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Construct a knowledge graph of UK public record\n",
        "We will utilize the UK Gazette API to search for notices. The Notice Feed API is publicly available and doesn't require any authorization. However, you need to pretend you are a browser for it to work for some reason. I have no idea the reason behind this, but I spent 30 minutes of my life trying to make it work. The documentation of the API is available on [GitHub](https://github.com/TheGazette/DevDocs/blob/master/notice/notice-feed.md).\n",
        "\n",
        "The main two parameters to filter notices via the API are the category code and notice type. The category code is the higher-level filter, while the notice type allows you to select only a subsection of a category. The complete list of category codes and notice types is available on the following website. There is a broad selection of notices you can choose from, ranging from State and Parliament to Companies regulation and more.\n",
        "As mentioned, we can download the linked data format information for each notice. A nice thing about the Neosemantics library is that it can fetch data from local files as well as simple APIs. The workflow will be the following.\n",
        "Use the Notice Feed API to find relevant notice ids\n",
        "Use the Neosemantics to extract RDF information about specified notice ids and store it in Neo4j.\n",
        "\n",
        "Lastly, we will define the function that will take in the category code and notice type parameters and store the information about notices in the Neo4j database."
      ],
      "metadata": {
        "id": "FtDcKMiMlBaW"
      },
      "id": "FtDcKMiMlBaW"
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "id": "881392a2",
      "metadata": {
        "id": "881392a2"
      },
      "outputs": [],
      "source": [
        "import requests\n",
        "from requests.structures import CaseInsensitiveDict\n",
        "\n",
        "# Query to import RDF/XML data to Neo4j using Neosemantics\n",
        "import_rdf_query = \"\"\"\n",
        "UNWIND $data AS link\n",
        "CALL n10s.rdf.import.fetch(\n",
        "  link,\n",
        "  'RDF/XML'\n",
        ") YIELD triplesLoaded\n",
        "RETURN sum(triplesLoaded) AS totalTriplesLoaded\n",
        "\"\"\"\n",
        "\n",
        "def make_request(uri):\n",
        "    # For some reason, the API only works when I pretend to be a browser\n",
        "    headers = CaseInsensitiveDict()\n",
        "    headers[\"user-agent\"] = \"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.45 Safari/537.36\"\n",
        "    return requests.get(uri, headers=headers)\n",
        "    \n",
        "def ukgazzette_to_neo4j(pages=1, categorycode=\"\", noticetype=\"\"):\n",
        "    for page in range(1, pages + 1):\n",
        "        baseUrl = f\"https://www.thegazette.co.uk/all-notices/notice/data.json\"\n",
        "        ccode = \"categorycode=\" + categorycode + \"&\" if categorycode else \"\"\n",
        "        ntype = \"noticetype=\" + noticetype + \"&\" if noticetype else \"\"\n",
        "        parameters = f\"?{ccode}{ntype}results-page-size=100&sort-by=latest-date&results-page={page}\"\n",
        "        \n",
        "        try:\n",
        "            response = make_request(baseUrl + parameters)\n",
        "            responseJson = response.json()\n",
        "        except Exception as e:\n",
        "            print(response.text)\n",
        "            print(e)\n",
        "            break\n",
        "\n",
        "        # Define RDF/XML URL links\n",
        "        data = []\n",
        "        for notice in responseJson['entry']:\n",
        "            id = notice['id'].split('/')[-1]\n",
        "            rdf_uri = f\"https://www.thegazette.co.uk/notice/{id}/data.rdf?view=linked-data\"\n",
        "            data.append(rdf_uri)\n",
        "\n",
        "        # Import RDF into Neo4j with Neosemantics\n",
        "        query_response = run_query(import_rdf_query, {'data': data})\n",
        "        print(query_response)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The Cypher statement expects the data parameter to contain a list of links where the RDF/XML information about the notices is available. The Neosemantics library supports other RDF serialization formats as well, such as JSON-LD and others, if you might be wondering.\n",
        "\n",
        "We get 100 notice ids for each request to the Notice Feed API. I've included the pagination feature in the function if you want to import more. As you might see from the code, we make a request to the notice feed and construct a list of links where the RDF/XML information about notices is stored. Next, we input that list as the parameter to the Cypher statement, where the Neosemantics library will iterate over all the links and store the information in Neo4j. It's about as simple as it gets.\n",
        "Now we can go ahead and import the last 1000 notices under the state category code. If you look at the notice code reference, you can see that the state notices fall under the category code value of 11."
      ],
      "metadata": {
        "id": "JjrCVPYSlMe0"
      },
      "id": "JjrCVPYSlMe0"
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "id": "4bae416b",
      "metadata": {
        "id": "4bae416b",
        "outputId": "aecb1951-0b89-4e8d-b3e7-cda614874b5a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   totalTriplesLoaded\n",
            "0                4656\n",
            "   totalTriplesLoaded\n",
            "0                5129\n",
            "   totalTriplesLoaded\n",
            "0                5129\n",
            "   totalTriplesLoaded\n",
            "0                5157\n",
            "   totalTriplesLoaded\n",
            "0                5340\n",
            "   totalTriplesLoaded\n",
            "0                5105\n",
            "   totalTriplesLoaded\n",
            "0                5101\n",
            "   totalTriplesLoaded\n",
            "0                5060\n",
            "   totalTriplesLoaded\n",
            "0                5022\n",
            "   totalTriplesLoaded\n",
            "0                5116\n"
          ]
        }
      ],
      "source": [
        "# Import last 1000 state notices\n",
        "ukgazzette_to_neo4j(10, \"11\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The graph schema of the imported graph is complex and not easy to visualize so we will skip that. I didn't spend much time apprehending the whole graph structure, but I prepared a couple of sample Cypher statements that could get us started.\n",
        "\n",
        "For example, we can examine the last five receivers of any awards."
      ],
      "metadata": {
        "id": "vdqKcaPplUNT"
      },
      "id": "vdqKcaPplUNT"
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "id": "73c24c18",
      "metadata": {
        "id": "73c24c18",
        "outputId": "8f4a74e0-8ded-4da7-db28-4b8b34703688",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      award relatedDate                         person jobTitle organization\n",
              "0    K.P.M.  2022-12-31          Lindsay Leanne FISHER     None         None\n",
              "1    K.A.M.  2022-12-31  Edward Michael Rhodri O’BRIAN     None         None\n",
              "2  K.F.S.M.  2022-12-31                  Robert STRANG     None         None\n",
              "3  K.F.S.M.  2022-12-31                   John ROBERTS     None         None\n",
              "4    K.P.M.  2022-12-31                      Alan TODD     None         None"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-77caecec-481a-4296-a6de-05ac3402ac05\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>award</th>\n",
              "      <th>relatedDate</th>\n",
              "      <th>person</th>\n",
              "      <th>jobTitle</th>\n",
              "      <th>organization</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>K.P.M.</td>\n",
              "      <td>2022-12-31</td>\n",
              "      <td>Lindsay Leanne FISHER</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>K.A.M.</td>\n",
              "      <td>2022-12-31</td>\n",
              "      <td>Edward Michael Rhodri O’BRIAN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>K.F.S.M.</td>\n",
              "      <td>2022-12-31</td>\n",
              "      <td>Robert STRANG</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>K.F.S.M.</td>\n",
              "      <td>2022-12-31</td>\n",
              "      <td>John ROBERTS</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>K.P.M.</td>\n",
              "      <td>2022-12-31</td>\n",
              "      <td>Alan TODD</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-77caecec-481a-4296-a6de-05ac3402ac05')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-77caecec-481a-4296-a6de-05ac3402ac05 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-77caecec-481a-4296-a6de-05ac3402ac05');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "# Who has received any awards\n",
        "\n",
        "run_query(\"\"\"\n",
        "MATCH (award)<-[:ISAWARDED]-(t:AwardandHonourThing)-[:HASAWARDEE]->(person)-[:HASEMPLOYMENT]->(employment)-[:ISMEMBEROFORGANISATION]->(organization)\n",
        "RETURN award.label AS award,\n",
        "       t.relatedDate AS relatedDate,\n",
        "       person.name AS person,\n",
        "       employment.jobTitle AS jobTitle,\n",
        "       organization.name AS organization\n",
        "ORDER BY relatedDate DESC\n",
        "LIMIT 5\n",
        "\"\"\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Remember, these was the example I showed in the introduction of this article. I've learned that one can also be appointed as the commander in the Order of the British Empire."
      ],
      "metadata": {
        "id": "nrdpb8VJlXe1"
      },
      "id": "nrdpb8VJlXe1"
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "id": "17d7f94b",
      "metadata": {
        "id": "17d7f94b",
        "outputId": "6d99b980-90c6-4ef1-c9ad-34a891cad43a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 49
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [award, date, appointee, authority]\n",
              "Index: []"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-5e964468-fee8-492b-b5d4-f992f2d2768f\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>award</th>\n",
              "      <th>date</th>\n",
              "      <th>appointee</th>\n",
              "      <th>authority</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5e964468-fee8-492b-b5d4-f992f2d2768f')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-5e964468-fee8-492b-b5d4-f992f2d2768f button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-5e964468-fee8-492b-b5d4-f992f2d2768f');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "MATCH (n:CommanderOrderOfTheBritishEmpire)<-[:ISAPPOINTEDAS]-(notice)-[:HASAPPOINTEE]->(appointee),\n",
        "      (notice)-[:HASAUTHORITY]->(authority)\n",
        "RETURN n.label AS award,\n",
        "       notice.relatedDate AS date,\n",
        "       appointee.name AS appointee,\n",
        "       authority.label AS authority\n",
        "ORDER BY date DESC\n",
        "LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "To steer away from awards, we can also inspect which notices that are related to various legislation."
      ],
      "metadata": {
        "id": "gMzt2ERelZd-"
      },
      "id": "gMzt2ERelZd-"
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "id": "8d4addad",
      "metadata": {
        "id": "8d4addad",
        "outputId": "701fac47-b9c7-4911-fe2d-a5a1b680af69",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 49
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [noticeID, noticeURI, date, provenance, relatedLegislations]\n",
              "Index: []"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3815d7f8-4c63-425c-8657-7ac6425599a4\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>noticeID</th>\n",
              "      <th>noticeURI</th>\n",
              "      <th>date</th>\n",
              "      <th>provenance</th>\n",
              "      <th>relatedLegislations</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3815d7f8-4c63-425c-8657-7ac6425599a4')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-3815d7f8-4c63-425c-8657-7ac6425599a4 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-3815d7f8-4c63-425c-8657-7ac6425599a4');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "MATCH (provenance)<-[:HAS_PROVENANCE]-(n:Notice)-[:ISABOUT]->(l:Legislation:NotifiableThing)-[:RELATEDLEGISLATION]->(related)\n",
        "RETURN n.hasNoticeID AS noticeID,\n",
        "       n.uri AS noticeURI,\n",
        "       l.relatedDate AS date,\n",
        "       provenance.uri AS provenance,\n",
        "       collect(related.label) AS relatedLegislations\n",
        "ORDER BY date DESC\n",
        "LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The nice thing about our knowledge graph is that it contains all the data references to the Gazette website. This allows us to verify and also find more information if needed. In addition, through my data exploration, I've noticed that not all information is parsed from notices as a lot of information is hard to structure as a graph automatically. More on that later.\n",
        "Suppose you are like me and get quickly bored by state information. In that case, you could fetch more business-related information such as companies buying back their own stock, company directors being disqualified, or partnership dissolutions."
      ],
      "metadata": {
        "id": "hu31-J0flcTz"
      },
      "id": "hu31-J0flcTz"
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "id": "9aae952c",
      "metadata": {
        "id": "9aae952c",
        "outputId": "8d6a14ec-2b0c-4bd1-e51e-289daa09104a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   totalTriplesLoaded\n",
            "0                4100\n",
            "   totalTriplesLoaded\n",
            "0                3883\n",
            "   totalTriplesLoaded\n",
            "0                3945\n",
            "   totalTriplesLoaded\n",
            "0                4025\n",
            "   totalTriplesLoaded\n",
            "0                4127\n",
            "   totalTriplesLoaded\n",
            "0                4517\n",
            "   totalTriplesLoaded\n",
            "0                4023\n",
            "   totalTriplesLoaded\n",
            "0                3872\n",
            "   totalTriplesLoaded\n",
            "0                3745\n",
            "   totalTriplesLoaded\n",
            "0                4242\n"
          ]
        }
      ],
      "source": [
        "# Redemption or purchase of own shares out of capital, Company director disqualification order, Dissolution of partnership\n",
        "ukgazzette_to_neo4j(10, \"26+27\", \"2602+2608+2702\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "I've spent 30 minutes figuring out how to properly use notice types and category code parameters to filter notice feeds. You must also include the category code parameter when you want to filter by notice type. Otherwise, the filtering won't work as expected.\n",
        "We don't have to worry about creating separate graphs or databases for additional notice feeds. The graph schema is already defined in the RDF/XML data structure, and you can import all the notice types into a single Neo4j instance.\n",
        "\n",
        "Now you can examine which partnerships have dissolved."
      ],
      "metadata": {
        "id": "xAaIdk3QlfJY"
      },
      "id": "xAaIdk3QlfJY"
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "id": "ef715f90",
      "metadata": {
        "id": "ef715f90",
        "outputId": "ab4caa9a-7f20-473a-920a-5c7ef707c22f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  noticeID        date                                          noticeURI  \\\n",
              "0  4269576  2023-01-31  https://www.thegazette.co.uk/id/notice/4269576...   \n",
              "1  4269885  2023-01-31  https://www.thegazette.co.uk/id/notice/4269885...   \n",
              "2  4269565  2023-01-24  https://www.thegazette.co.uk/id/notice/4269565...   \n",
              "3  4258085  2023-01-04  https://www.thegazette.co.uk/id/notice/4258085...   \n",
              "4  4249626  2022-12-30  https://www.thegazette.co.uk/id/notice/4249626...   \n",
              "\n",
              "             enablingLegislation                             partnership  \n",
              "0  LIMITED PARTNERSHIPS ACT 1907                               AF VII LP  \n",
              "1  LIMITED PARTNERSHIPS ACT 1907                            Viking DS LP  \n",
              "2  LIMITED PARTNERSHIPS ACT 1907  LEHMAN BROTHERS HOLDINGS SCOTTISH LP 3  \n",
              "3  LIMITED PARTNERSHIPS ACT 1907             ALPINA PARTNERS (SCOTGP) LP  \n",
              "4  LIMITED PARTNERSHIPS ACT 1907                       RANCH CAPITAL SLP  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-60ea0c98-a8e8-4619-8f19-e37cb99f0972\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>noticeID</th>\n",
              "      <th>date</th>\n",
              "      <th>noticeURI</th>\n",
              "      <th>enablingLegislation</th>\n",
              "      <th>partnership</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>4269576</td>\n",
              "      <td>2023-01-31</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4269576...</td>\n",
              "      <td>LIMITED PARTNERSHIPS ACT 1907</td>\n",
              "      <td>AF VII LP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4269885</td>\n",
              "      <td>2023-01-31</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4269885...</td>\n",
              "      <td>LIMITED PARTNERSHIPS ACT 1907</td>\n",
              "      <td>Viking DS LP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4269565</td>\n",
              "      <td>2023-01-24</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4269565...</td>\n",
              "      <td>LIMITED PARTNERSHIPS ACT 1907</td>\n",
              "      <td>LEHMAN BROTHERS HOLDINGS SCOTTISH LP 3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4258085</td>\n",
              "      <td>2023-01-04</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4258085...</td>\n",
              "      <td>LIMITED PARTNERSHIPS ACT 1907</td>\n",
              "      <td>ALPINA PARTNERS (SCOTGP) LP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4249626</td>\n",
              "      <td>2022-12-30</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4249626...</td>\n",
              "      <td>LIMITED PARTNERSHIPS ACT 1907</td>\n",
              "      <td>RANCH CAPITAL SLP</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-60ea0c98-a8e8-4619-8f19-e37cb99f0972')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-60ea0c98-a8e8-4619-8f19-e37cb99f0972 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-60ea0c98-a8e8-4619-8f19-e37cb99f0972');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "MATCH (n:PartnershipDissolutionNotice)-[:ISABOUT]->(notifiableThing)-[:HASCOMPANY]->(partnership),\n",
        "      (notifiableThing)-[:ISENABLEDBYLEGISLATION]->(enabledby)\n",
        "RETURN n.hasNoticeID AS noticeID,\n",
        "       notifiableThing.relatedDate AS date,\n",
        "       notifiableThing.uri AS noticeURI,\n",
        "       enabledby.label AS enablingLegislation,\n",
        "       partnership.name AS partnership\n",
        "ORDER BY date DESC\n",
        "LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Another interesting information is about which companies have or intend to buyback their own shares."
      ],
      "metadata": {
        "id": "4sa1TSIdlh4e"
      },
      "id": "4sa1TSIdlh4e"
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "id": "442ff7fc",
      "metadata": {
        "id": "442ff7fc",
        "outputId": "e222a56d-92a2-4ea2-ab11-92ab70f32909",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         date                                  company  \\\n",
              "0  2022-11-22   TREASURYSPRING LIMITED\\n\\n\\n             \n",
              "1  2022-09-23  \\n          EXPRESS HEATING CO. LIMITED   \n",
              "\n",
              "                                        companyURI  \\\n",
              "0  http://business.data.gov.uk/id/company/10373508   \n",
              "1  http://business.data.gov.uk/id/company/SC046408   \n",
              "\n",
              "                                relatedLegislations  \\\n",
              "0  [Companies Act 2006, s. 719, COMPANIES ACT 2006]   \n",
              "1  [COMPANIES ACT 2006, Companies Act 2006, s. 719]   \n",
              "\n",
              "                                           noticeURI  \n",
              "0  https://www.thegazette.co.uk/id/notice/4215745...  \n",
              "1  https://www.thegazette.co.uk/id/notice/4166832...  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-31fb0957-a23c-4486-9f83-bed13314293c\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>company</th>\n",
              "      <th>companyURI</th>\n",
              "      <th>relatedLegislations</th>\n",
              "      <th>noticeURI</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2022-11-22</td>\n",
              "      <td>TREASURYSPRING LIMITED\\n\\n\\n</td>\n",
              "      <td>http://business.data.gov.uk/id/company/10373508</td>\n",
              "      <td>[Companies Act 2006, s. 719, COMPANIES ACT 2006]</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4215745...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2022-09-23</td>\n",
              "      <td>\\n          EXPRESS HEATING CO. LIMITED</td>\n",
              "      <td>http://business.data.gov.uk/id/company/SC046408</td>\n",
              "      <td>[COMPANIES ACT 2006, Companies Act 2006, s. 719]</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4166832...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-31fb0957-a23c-4486-9f83-bed13314293c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-31fb0957-a23c-4486-9f83-bed13314293c button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-31fb0957-a23c-4486-9f83-bed13314293c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "MATCH (legislation)<-[:RELATEDLEGISLATION]-(n:RedemptionOrPurchase)-[:HASCOMPANY]->(company)\n",
        "RETURN n.relatedDate AS date,\n",
        "       company.name AS company,\n",
        "       company.uri AS companyURI,\n",
        "       collect(legislation.label) AS relatedLegislations,\n",
        "       n.uri AS noticeURI\n",
        "ORDER BY date DESC\n",
        "LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Taking in to the next level\n",
        "As mentioned before, there are some example where not all information is extracted from notices in a linked data structure. One such example are the members change in partnership. We have the information about the partnership in which the membership changed, but not exactly what has changed. All the data we can retrieve is the following:"
      ],
      "metadata": {
        "id": "v3hsHfgmlkau"
      },
      "id": "v3hsHfgmlkau"
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "id": "4736255c",
      "metadata": {
        "id": "4736255c",
        "outputId": "0fe11482-2d6e-46c2-8d0e-bbb079443b0a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  noticeID                                       noticeURI        date  \\\n",
              "0  4255004  https://www.thegazette.co.uk/id/notice/4255004  2023-01-10   \n",
              "1  4255003  https://www.thegazette.co.uk/id/notice/4255003  2023-01-10   \n",
              "2  4256953  https://www.thegazette.co.uk/id/notice/4256953  2023-01-01   \n",
              "3  4256950  https://www.thegazette.co.uk/id/notice/4256950  2023-01-01   \n",
              "4  4247940  https://www.thegazette.co.uk/id/notice/4247940  2022-12-23   \n",
              "\n",
              "                                            company  \n",
              "0                     \\n          ACTONS SOLICITORS  \n",
              "1                     \\n          ACTONS SOLICITORS  \n",
              "2  \\nESPRIT CAPITAL I FUND NO.2 LIMITED PARTNERSHIP  \n",
              "3    ESPRIT CAPITAL I FUND NO.1 LIMITED PARTNERSHIP  \n",
              "4                       GLG PARTNERS LP\\n            "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-62dcbe7f-b296-4388-8f49-fd06f4cb94fa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>noticeID</th>\n",
              "      <th>noticeURI</th>\n",
              "      <th>date</th>\n",
              "      <th>company</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>4255004</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4255004</td>\n",
              "      <td>2023-01-10</td>\n",
              "      <td>\\n          ACTONS SOLICITORS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4255003</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4255003</td>\n",
              "      <td>2023-01-10</td>\n",
              "      <td>\\n          ACTONS SOLICITORS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4256953</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4256953</td>\n",
              "      <td>2023-01-01</td>\n",
              "      <td>\\nESPRIT CAPITAL I FUND NO.2 LIMITED PARTNERSHIP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4256950</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4256950</td>\n",
              "      <td>2023-01-01</td>\n",
              "      <td>ESPRIT CAPITAL I FUND NO.1 LIMITED PARTNERSHIP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4247940</td>\n",
              "      <td>https://www.thegazette.co.uk/id/notice/4247940</td>\n",
              "      <td>2022-12-23</td>\n",
              "      <td>GLG PARTNERS LP\\n</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-62dcbe7f-b296-4388-8f49-fd06f4cb94fa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-62dcbe7f-b296-4388-8f49-fd06f4cb94fa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-62dcbe7f-b296-4388-8f49-fd06f4cb94fa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ],
      "source": [
        "run_query(\"\"\"\n",
        "MATCH (notice)-[:ISABOUT]->(n:PartnershipChangeInMembers)-[:HASCOMPANY]->(company)\n",
        "RETURN notice.hasNoticeID AS noticeID,\n",
        "       notice.uri AS noticeURI,\n",
        "       n.relatedDate AS date,\n",
        "       company.name AS company\n",
        "ORDER BY date DESC\n",
        "LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "For example, if we inspect the first notice on the website we can observe that a the actual changes are not available in the linked data format.\n",
        "\n",
        "I totally understand why the actual changes are not in a structured format. The reason is that there are too many variations of the membership change notice to capture them all in a structured way.\n",
        "\n",
        "It seems like all the roads lead to Rome, or in our case, when dealing with text, you will possibly have to utilize NLP techniques. So I've added a simple example of using SpaCy to extract organizations and person entities from notices."
      ],
      "metadata": {
        "id": "ArnuibSdlnw1"
      },
      "id": "ArnuibSdlnw1"
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "id": "64405655",
      "metadata": {
        "id": "64405655",
        "outputId": "102d127c-34c1-44bf-a5dc-e998c1159912",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.8/dist-packages/torch/cuda/__init__.py:497: UserWarning: Can't initialize NVML\n",
            "  warnings.warn(\"Can't initialize NVML\")\n"
          ]
        }
      ],
      "source": [
        "from bs4 import BeautifulSoup as bs\n",
        "import spacy\n",
        "\n",
        "nlp = spacy.load(\"en_core_web_sm\")\n",
        "\n",
        "def extract_entities(noticeId):\n",
        "    print(f\"\\nExtracting entities for {noticeId}\")\n",
        "    uri = f\"https://www.thegazette.co.uk/notice/{noticeId}/data.xml?download=true\"\n",
        "    content = make_request(uri).content\n",
        "    bs_content = bs(content, \"lxml\")\n",
        "    text = \" \".join([el.text for el in bs_content.findAll(\"p\", {\"data-gazettes\":\"Text\"})])\n",
        "    print(text)\n",
        "    doc = nlp(text)\n",
        "    # Find named entities, phrases and concepts\n",
        "    print('Entities \\n --------------------')\n",
        "    for entity in doc.ents:\n",
        "        if not entity.label_ in ['PERSON', 'ORG']:\n",
        "            continue\n",
        "        print(entity.text, entity.label_)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The text of notices is not stored in our knowledge graph, so we have to utilize the UK Gazette API to retrieve it. I've used BeatifulSoup to extract the text from the XML response and then run it through SpaCy's NLP pipeline to detect organizations and person mentions. The code doesn't store the entities back to Neo4j. I've just wanted to give you a simple example of how you could start utilizing NLP capabilities to extract more information.\n",
        "\n",
        "We can now detect entities for a couple of changes in members of partnership notices."
      ],
      "metadata": {
        "id": "rD7vVn7Olssz"
      },
      "id": "rD7vVn7Olssz"
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "id": "a9a89af5",
      "metadata": {
        "id": "a9a89af5",
        "outputId": "263b8218-7b85-4e42-d90f-5b43d2f4d4d0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "Extracting entities for 4270330\n",
            "NOTICE IS HEREBY GIVEN of the Partnership heretofore subsisting between John Joseph Easter, Jonathan Norman Richards and Surbjit Kumar carrying on business as Solicitors at 1 Rose Hill, Willenhall, West Midlands, WV13 2AR under the style or firm of ROWLAND TILDESLEY & HARRIS has been dissolved by mutual consent as from the 31st January 2023 so far as concerns the said John Joseph Easter who retires from the firm and that all debts due to and owing by the said firm will be received and paid by the said continuing partners who will continue to carry on the said business under the same style or firm. Dated this 1st day of February 2023\n",
            "Entities \n",
            " --------------------\n",
            "John Joseph Easter PERSON\n",
            "Jonathan Norman Richards PERSON\n",
            "Surbjit Kumar PERSON\n",
            "Rose Hill PERSON\n",
            "ROWLAND TILDESLEY & HARRIS ORG\n",
            "John Joseph Easter PERSON\n",
            "\n",
            "Extracting entities for 4263635\n",
            "\n",
            "          Pursuant to section 10 of the Limited Partnerships Act 1907, notice is hereby given in respect of IIF UK 1 LP, a limited partnership registered in England with registered number LP012764 (the “Partnership”), that:\n",
            " Effective 31 December 2022, Fondation de Prévoyance Richemont was admitted as a limited partner to the Partnership. \n",
            "Effective 1 January 2023, BMT Pension Trustee Limited in its capacity as trustee of British Maritime Technology Pension Fund Life Assurance Scheme was admitted as a limited partner to the Partnership.\n",
            "\n",
            "Entities \n",
            " --------------------\n",
            "IIF UK 1 LP ORG\n",
            "Fondation de Prévoyance Richemont ORG\n",
            "Partnership ORG\n",
            "BMT Pension Trustee Limited ORG\n",
            "British Maritime Technology Pension Fund Life Assurance Scheme ORG\n",
            "Partnership ORG\n",
            "\n",
            "Extracting entities for 4260898\n",
            "Notice is hereby given that with effect from 31 October 2022 Dr. Susan Hesketh retired from the partnership known as Minfor Surgery, Park Road, Barmouth, LL42 1PL. The remaining Partners will continue to carry on the said partnership under the same name.\n",
            "Entities \n",
            " --------------------\n",
            "Notice ORG\n",
            "Susan Hesketh PERSON\n",
            "Minfor Surgery PERSON\n",
            "Barmouth PERSON\n",
            "\n",
            "Extracting entities for 4260897\n",
            "Announcing the retirement of Paul Martin Monaghan from his position of Partner at Troup Bywaters + Anders, effective 31st December 2022\n",
            "Entities \n",
            " --------------------\n",
            "Paul Martin Monaghan PERSON\n",
            "Partner at Troup Bywaters + Anders ORG\n",
            "\n",
            "Extracting entities for 4260896\n",
            "\n",
            "\n",
            "\n",
            "Greenslade Taylor Hunt \n",
            "\n",
            "Notice is hereby given that ANTHONY MICHAEL OVERHILL and JEREMY BEVAN BELL both retired on 31st December 2022 and ceased to be a Partner of Greenslade Taylor Hunt, a Partnership having a place of business at 9 Hammet Street, Taunton, Somerset, TA1 1RZ. The remaining Partners of Greenslade Taylor Hunt will continue to practice from the firms existing offices.\n",
            "Entities \n",
            " --------------------\n",
            "Greenslade Taylor Hunt \n",
            "\n",
            "Notice PERSON\n",
            "ANTHONY MICHAEL OVERHILL PERSON\n",
            "JEREMY BEVAN BELL PERSON\n",
            "Taylor Hunt PERSON\n",
            "Taylor Hunt PERSON\n"
          ]
        }
      ],
      "source": [
        "partnership_changes = run_query(\"\"\"\n",
        "MATCH (notice)-[:ISABOUT]->(n:PartnershipChangeInMembers)\n",
        "RETURN notice.hasNoticeID AS noticeID\n",
        "LIMIT 5\n",
        "\"\"\")['noticeID'].to_list()\n",
        "\n",
        "for i in partnership_changes:\n",
        "    extract_entities(i)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The NLP pipeline doesn't extract specific changes, but at least it's a start since you can't create a rule-based difference extraction due to having a non-standard structure of the text. You can observe that even the two examples are wildly different in text structure and information."
      ],
      "metadata": {
        "id": "WraCl3R_lv1I"
      },
      "id": "WraCl3R_lv1I"
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "47afd616",
      "metadata": {
        "id": "47afd616"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.8"
    },
    "colab": {
      "name": "UK Gazette.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}